package com.dzz.sjgl.service.impl;

import com.dzz.sjgl.entity.Dictionary;
import com.dzz.sjgl.entity.SentenceInfo;
import com.dzz.sjgl.jpa.DictRepository;
import com.dzz.sjgl.jpa.SentenceRepository;
import com.dzz.sjgl.service.ISentenceService;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.criteria.Predicate;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * @author KRIC
 * @ClassName UserServiceImpl
 * @Date: 2018-8-1 17:25
 * @Description:
 */
@Service
public class SentenceServiceImpl implements ISentenceService {
    private Logger logger = LoggerFactory.getLogger(this.getClass());
    private final SentenceRepository sentenceRepository;
    private final DictRepository dictRepository;
    @PersistenceContext
    EntityManager entityManager;

    @Autowired
    public SentenceServiceImpl(SentenceRepository sentenceRepository, DictRepository dictRepository) {
        this.sentenceRepository = sentenceRepository;
        this.dictRepository = dictRepository;
    }


    /**
     * 保存更新
     */
    @Override
    public SentenceInfo save(SentenceInfo sentenceInfo) {
        return sentenceRepository.save(sentenceInfo);
    }


    /**
     * 分页
     */
    @Override
    public Map<String, Object> findAllByQuery(Integer pageNumber, Integer pageSize, String sentence, Integer paperId, String province, String exam, Integer minYear, Integer maxYear) {
        Map<String, Object> map = new HashMap<>();
        StringBuilder baseSql = new StringBuilder();
        baseSql.append(" FROM sentence_info s LEFT JOIN paper_info p ON s.paper_id = p.id ");
        boolean sentenceB = !StringUtils.isEmpty(sentence);
        boolean paperIdB = paperId != null;
        boolean provinceB = !StringUtils.isEmpty(province);
        boolean examB = !StringUtils.isEmpty(exam);
        boolean minYearB = minYear != null;
        boolean maxYearB = maxYear != null;
        if (sentenceB || paperIdB || provinceB || examB || minYearB || maxYearB) {
            baseSql.append(" where ");
            if (sentenceB) {
                baseSql.append(" s.sentence like '%").append(sentence.trim()).append("%' ");
            }
            if (paperIdB) {
                if (sentenceB) {
                    baseSql.append(" and ");
                }
                baseSql.append(" s.paper_id = ").append(paperId).append(" ");
            }
            if (provinceB) {
                if (sentenceB || paperIdB) {
                    baseSql.append(" and ");
                }
                baseSql.append(" p.province = '").append(province.trim()).append("' ");
            }
            if (examB) {
                if (sentenceB || paperIdB || provinceB) {
                    baseSql.append(" and ");
                }
                baseSql.append(" p.exam = '").append(exam.trim()).append("' ");
            }
            if (minYearB) {
                if (sentenceB || paperIdB || provinceB || examB) {
                    baseSql.append(" and ");
                }
                baseSql.append(" p.year >= ").append(minYear).append(" ");
            }
            if (maxYearB) {
                if (sentenceB || paperIdB || provinceB || examB || minYearB) {
                    baseSql.append(" and ");
                }
                baseSql.append(" p.year <= ").append(maxYear).append(" ");
            }
        }
        String querySql = " SELECT s.* " + baseSql;
        if (pageNumber != 0 && pageSize != 0) {
            querySql = querySql + " LIMIT " + (pageNumber - 1) * pageSize + "," + pageSize;
            String countSql = " SELECT count(*)  " + baseSql;
            Query countQuery = entityManager.createNativeQuery(countSql);
            BigInteger count = (BigInteger) countQuery.getSingleResult();
            int totalElements = count.intValue();
            int totalPages = totalElements / pageSize + (totalElements % pageSize == 0 ? 0 : 1);
            map.put("totalPages", totalPages);
            map.put("totalElements", totalElements);
            entityManager.clear();
        }
        Query query = entityManager.createNativeQuery(querySql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List list = query.getResultList();
        entityManager.close();
        map.put("content", list);
        return map;
    }

    /**
     * 单词 统计
     */
    @Override
    public Map<String, Object> getCount(String word) {
        Map<String, Object> map = new HashMap<>();
        map.put("word", word);
//        long countAll = sentenceRepository.count((Specification<SentenceInfo>) (root, query, criteriaBuilder) -> {
//            List<Predicate> predicateList = new ArrayList<>();
//            if (!StringUtils.isEmpty(word)) {
//                predicateList.add(criteriaBuilder.like(root.get("sentence").as(String.class), "%" + word.trim() + "%"));
//            }
//            return criteriaBuilder.and(predicateList.toArray(new Predicate[0]));
//        });
//        map.put("countAll", countAll);
        List<Map<String, String>> list = sentenceRepository.getCount(word);
        map.put("list", list);
        return map;
    }

    /**
     * 词频 统计
     */
    @Override
    public Map<String, Object> getCp(int pageNum, int pageSize, String word, Integer minCp, String province, String exam, Integer minYear, Integer maxYear) {
        Map<String, Object> map = new HashMap<>();
        StringBuilder baseSql = new StringBuilder();
        baseSql.append(" SELECT w.id, w.word,count(*) count ")
                .append(" FROM word_info w LEFT JOIN paper_word pw ON w.id = pw.word_id ")
                .append(" LEFT JOIN paper_info p ON p.id = pw.paper_id ");
        boolean wordB = !StringUtils.isEmpty(word);
        boolean provinceB = !StringUtils.isEmpty(province);
        boolean examB = !StringUtils.isEmpty(exam);
        boolean minYearB = minYear != null;
        boolean maxYearB = maxYear != null;
        if (wordB || provinceB || examB || minYearB || maxYearB) {
            baseSql.append(" where ");
            if (wordB) {
                baseSql.append(" w.word = '").append(word.trim()).append("' ");
            }
            if (provinceB) {
                if (wordB) {
                    baseSql.append(" and ");
                }
                baseSql.append(" p.province = '").append(province.trim()).append("' ");
            }
            if (examB) {
                if (wordB || provinceB) {
                    baseSql.append(" and ");
                }
                baseSql.append(" p.exam = '").append(exam.trim()).append("' ");
            }
            if (minYearB) {
                if (wordB || provinceB || examB) {
                    baseSql.append(" and ");
                }
                baseSql.append(" p.year >= ").append(minYear).append(" ");
            }
            if (maxYearB) {
                if (wordB || provinceB || examB || minYearB) {
                    baseSql.append(" and ");
                }
                baseSql.append(" p.year <= ").append(maxYear).append(" ");
            }
        }
        baseSql.append(" GROUP BY w.word,w.id ");
        if (minCp != null) {
            baseSql.append(" HAVING count >= ").append(minCp).append(" ");
        }
        String querySql = baseSql + " ORDER BY count DESC ,w.word  ";
        if (pageNum != 0 && pageSize != 0) {
            String countSql = " SELECT count(*)  FROM ( " + baseSql + " ) a";
            querySql = querySql + " LIMIT " + (pageNum - 1) * pageSize + "," + pageSize;
            Query countQuery = entityManager.createNativeQuery(countSql);
            BigInteger count = (BigInteger) countQuery.getSingleResult();
            int totalElements = count.intValue();
            entityManager.clear();
            int totalPages = totalElements / pageSize + (totalElements % pageSize == 0 ? 0 : 1);
            map.put("totalPages", totalPages);
            map.put("totalElements", totalElements);
        }
        Query query = entityManager.createNativeQuery(querySql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List list = query.getResultList();
        entityManager.close();
        map.put("content", list);
        return map;
    }

    /**
     * 查看单词所在试卷信息
     */
    @Override
    public Map<String, Object> getPaperByWord(int pageNum, int pageSize, Integer wordId, String province, String exam, Integer minYear, Integer maxYear, String word) {
        Map<String, Object> map = new HashMap<>();
        StringBuilder baseSql = new StringBuilder();
        baseSql.append(" FROM word_info w LEFT JOIN paper_word pw ON w.id = pw.word_id ")
                .append(" LEFT JOIN paper_info p ON p.id = pw.paper_id ")
                .append(" LEFT JOIN dictionary d ON p.exam = d.code AND d.type='exam' ")
                .append(" LEFT JOIN dictionary di ON p.province = di.code AND di.type='province' ");
        boolean wordIdB = wordId != null;
        boolean provinceB = !StringUtils.isEmpty(province);
        boolean examB = !StringUtils.isEmpty(exam);
        boolean wordB = !StringUtils.isEmpty(word);
        boolean minYearB = minYear != null;
        boolean maxYearB = maxYear != null;
        if (wordIdB || provinceB || examB || minYearB || maxYearB || wordB) {
            baseSql.append(" where ");
            if (wordIdB) {
                baseSql.append(" w.id = ").append(wordId).append(" ");
            }
            if (provinceB) {
                if (wordIdB) {
                    baseSql.append(" and ");
                }
                baseSql.append(" p.province = '").append(province.trim()).append("' ");
            }
            if (examB) {
                if (wordIdB || provinceB) {
                    baseSql.append(" and ");
                }
                baseSql.append(" p.exam = '").append(exam.trim()).append("' ");
            }
            if (minYearB) {
                if (wordIdB || provinceB || examB) {
                    baseSql.append(" and ");
                }
                baseSql.append(" p.year >= ").append(minYear).append(" ");
            }
            if (maxYearB) {
                if (wordIdB || provinceB || examB || minYearB) {
                    baseSql.append(" and ");
                }
                baseSql.append(" p.year <= ").append(maxYear).append(" ");
            }
            if (wordB) {
                if (wordIdB || provinceB || examB || minYearB || maxYearB) {
                    baseSql.append(" and ");
                }
                baseSql.append(" w.word =  '").append(word).append("'  ");
            }
        }
        String querySql = " SELECT p.create_time, w.word, p.name,p.subjects, p.img_path, p.word_path, p.pdf_path ,p.year,d.name exam ,pw.word_count ,di.name province "
                + baseSql;
        if (pageNum != 0 && pageSize != 0) {
            querySql = querySql + " LIMIT " + (pageNum - 1) * pageSize + "," + pageSize;
            String countSql = " SELECT count(*)  " + baseSql;
            Query countQuery = entityManager.createNativeQuery(countSql);
            BigInteger count = (BigInteger) countQuery.getSingleResult();
            entityManager.clear();
            int totalElements = count.intValue();
            int totalPages = totalElements / pageSize + (totalElements % pageSize == 0 ? 0 : 1);
            map.put("totalPages", totalPages);
            map.put("totalElements", totalElements);
        }
        querySql = querySql + " order by p.year ";
        Query query = entityManager.createNativeQuery(querySql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List list = query.getResultList();
        entityManager.close();
        map.put("content", list);
        return map;
    }

    /**
     * 字典表通用查询
     */
    @Override
    public List<Dictionary> getDict(String type, String name, String code) {
        Sort sort = new Sort(Sort.Direction.ASC, "name");
        return dictRepository.findAll((Specification<Dictionary>) (root, query, criteriaBuilder) -> {
            List<Predicate> predicateList = new ArrayList<>();
            if (!StringUtils.isEmpty(name)) {
                predicateList.add(criteriaBuilder.like(root.get("name").as(String.class), "%" + name.trim() + "%"));
            }
            if (!StringUtils.isEmpty(type)) {
                predicateList.add(criteriaBuilder.equal(root.get("type").as(String.class), type.trim()));
            }
            if (!StringUtils.isEmpty(code)) {
                predicateList.add(criteriaBuilder.equal(root.get("code").as(String.class), code.trim()));
            }
            return criteriaBuilder.and(predicateList.toArray(new Predicate[0]));
        }, sort);
    }

    /**
     * 删除脏数据
     */
    @Override
    public int delDirtyData() {
        return sentenceRepository.delDirtyData();
    }


}
